﻿using System.Data;
using System.Data.SqlClient;
using Pantry.Commons;
using Pantry.Entity;

namespace Pantry.DataAccess
{
    public class DepartmentDao
    {
        private readonly SqlConnection _sqlConnection;
        public DepartmentDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }
        #region Method
        /// <summary>
        /// Selects the department by directorate id.
        /// </summary>
        /// <param name="directorateId">The directorate id.</param>
        /// <returns></returns>
        public DataTable SelectDepartmentByDirectorateId(int directorateId)
        {
            try
            {
                var sqlParam = new SqlParameter[1];
                sqlParam[0] = new SqlParameter("@DirectorateId", SqlDbType.Int) { Value = directorateId };
                var ds = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, DBSchemas.SqlGetAllDepartmentByDirectorateId,sqlParam);
                return (ds.Tables[0]);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                throw;
            }
        }

        /// <summary>
        /// Gets the department by id.
        /// </summary>
        /// <param name="departmentId">The department id.</param>
        /// <returns></returns>
        public DataTable GetDepartmentById(int departmentId)
        {
            try
            {
                var sqlParam = new SqlParameter[1];
                sqlParam[0] = new SqlParameter("@DepartmentId", SqlDbType.Int) { Value = departmentId };
                var ds = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, DBSchemas.SqlGetDepartmentById, sqlParam);
                return (ds.Tables[0]);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                throw;
            }
        }

        /// <summary>
        /// Adds the department.
        /// </summary>
        /// <param name="department">The department.</param>
        /// <returns></returns>
        public int AddDepartment(Department department)
        {
            try
            {
                var sqlParam = new SqlParameter[19];
                sqlParam[0] = new SqlParameter("@DirectorateId", SqlDbType.Int) { Value = department.DirectorateId };
                sqlParam[1] = new SqlParameter("@DepartmentName", SqlDbType.NVarChar) { Value = department.DepartmentName };
                sqlParam[2] = new SqlParameter("@ShortDescription", SqlDbType.NVarChar) { Value = department.ShortDescription };
                sqlParam[3] = new SqlParameter("@ContactId", SqlDbType.Int) { Value = department.ContactId };
                sqlParam[4] = new SqlParameter("@AddressLine1", SqlDbType.NVarChar) { Value = department.AddressLine1 };
                sqlParam[5] = new SqlParameter("@AddressLine2", SqlDbType.NVarChar) { Value = department.AddressLine2 };
                sqlParam[6] = new SqlParameter("@AddressLine3", SqlDbType.NVarChar) { Value = department.AddressLine3 };
                sqlParam[7] = new SqlParameter("@Postcode", SqlDbType.NVarChar) { Value = department.Postcode };
                sqlParam[8] = new SqlParameter("@Town", SqlDbType.NVarChar) { Value = department.Town };
                sqlParam[9] = new SqlParameter("@County", SqlDbType.NVarChar) { Value = department.County };
                sqlParam[10] = new SqlParameter("@Country", SqlDbType.Int) { Value = department.Country };
                sqlParam[11] = new SqlParameter("@TypeOfBusinessId", SqlDbType.Int) { Value = department.TypeOfBusinessId };
                sqlParam[12] = new SqlParameter("@SicCode", SqlDbType.NVarChar) { Value = department.SicCode };
                sqlParam[13] = new SqlParameter("@FullDescription", SqlDbType.NVarChar) { Value = department.FullDescription };
                sqlParam[14] = new SqlParameter("@PhoneNumber", SqlDbType.VarChar) { Value = department.PhoneNumber };
                sqlParam[15] = new SqlParameter("@Fax", SqlDbType.VarChar) { Value = department.Fax };
                sqlParam[16] = new SqlParameter("@Email", SqlDbType.NVarChar) { Value = department.Email };
                sqlParam[17] = new SqlParameter("@WebAddress", SqlDbType.NVarChar) { Value = department.WebAddress };
                sqlParam[18] = new SqlParameter("@IsActivated", SqlDbType.Bit) { Value = department.IsActivated };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, DBSchemas.SqlAddDepartment, sqlParam);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                throw;
            }
        }

        /// <summary>
        /// Updates the department.
        /// </summary>
        /// <param name="department">The department.</param>
        /// <returns></returns>
        public int UpdateDepartment(Department department)
        {
            try
            {
                var sqlParam = new SqlParameter[18];
                sqlParam[0] = new SqlParameter("@DepartmentName", SqlDbType.NVarChar) { Value = department.DepartmentName };
                sqlParam[1] = new SqlParameter("@ShortDescription", SqlDbType.NVarChar) { Value = department.ShortDescription };
                sqlParam[2] = new SqlParameter("@ContactId", SqlDbType.Int) { Value = department.ContactId };
                sqlParam[3] = new SqlParameter("@AddressLine1", SqlDbType.NVarChar) { Value = department.AddressLine1 };
                sqlParam[4] = new SqlParameter("@AddressLine2", SqlDbType.NVarChar) { Value = department.AddressLine2 };
                sqlParam[5] = new SqlParameter("@AddressLine3", SqlDbType.NVarChar) { Value = department.AddressLine3 };
                sqlParam[6] = new SqlParameter("@Postcode", SqlDbType.NVarChar) { Value = department.Postcode };
                sqlParam[7] = new SqlParameter("@Town", SqlDbType.NVarChar) { Value = department.Town };
                sqlParam[8] = new SqlParameter("@County", SqlDbType.NVarChar) { Value = department.County };
                sqlParam[9] = new SqlParameter("@Country", SqlDbType.Int) { Value = department.Country };
                sqlParam[10] = new SqlParameter("@TypeOfBusinessId", SqlDbType.Int) { Value = department.TypeOfBusinessId };
                sqlParam[11] = new SqlParameter("@SicCode", SqlDbType.NVarChar) { Value = department.SicCode };
                sqlParam[12] = new SqlParameter("@FullDescription", SqlDbType.NVarChar) { Value = department.FullDescription };
                sqlParam[13] = new SqlParameter("@PhoneNumber", SqlDbType.VarChar) { Value = department.PhoneNumber };
                sqlParam[14] = new SqlParameter("@Fax", SqlDbType.VarChar) { Value = department.Fax };
                sqlParam[15] = new SqlParameter("@Email", SqlDbType.NVarChar) { Value = department.Email };
                sqlParam[16] = new SqlParameter("@WebAddress", SqlDbType.NVarChar) { Value = department.WebAddress };
                sqlParam[17] = new SqlParameter("@DepartmentId", SqlDbType.Int) { Value = department.DepartmentId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, DBSchemas.SqlUpdateDepartment, sqlParam);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                throw;
            }
        }

        /// <summary>
        /// Marks the activated.
        /// </summary>
        /// <param name="isActivated">if set to <c>true</c> [is activated].</param>
        /// <param name="departmentId">The department id.</param>
        /// <returns></returns>
        public int MarkActivated(bool isActivated,int departmentId)
        {
            try
            {
                var sqlParam = new SqlParameter[2];
                sqlParam[0] = new SqlParameter("@IsActivated", SqlDbType.Bit) { Value = isActivated };
                sqlParam[1] = new SqlParameter("@DepartmentId", SqlDbType.Int) { Value = departmentId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, DBSchemas.SqlMarkActivated, sqlParam);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                throw;
            }
        }

        /// <summary>
        /// Checks the department name exists.
        /// </summary>
        /// <param name="departmentName">Name of the department.</param>
        /// <returns></returns>
        public int CheckDepartmentNameExists(string departmentName)
        {
            try
            {
                var sqlParam = new SqlParameter[1];
                sqlParam[0] = new SqlParameter("@DepartmentName", SqlDbType.NVarChar) { Value = departmentName };
                return (int)SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, DBSchemas.SqlCheckDepartmentNameExists, sqlParam);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                throw;
            }
        }

        #endregion
    }
}
